﻿Public Class CierreObraSocialDAO

    Public Shared Function insertarCierreObraSocial(ByVal MesId As Integer, ByVal CierreObraSocialAno As Integer, ByVal ObraSocialXPlanId As Integer, ByVal CierreObraSocialTotalRecetas As Integer, ByVal CierreObraSocialTotalFacturado As Double, ByVal CierreObraSocialTotalACargo As Double) As Boolean
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            cn = Conexion.Conectar
            Dim consulta As New SqlClient.SqlCommand
            With consulta
                .CommandText = "usp_cierreObraSocial"
                .CommandType = CommandType.StoredProcedure
                With .Parameters
                    .AddWithValue("@Accion", Constante.INSERTAR).DbType = DbType.Int32
                    .AddWithValue("@CierreObraSocialId", Constante.MENOSUNO).DbType = DbType.Int32
                    .AddWithValue("@MesId", MesId).DbType = DbType.Int32
                    .AddWithValue("@CierreObraSocialAno", CierreObraSocialAno).DbType = DbType.Int32
                    .AddWithValue("@ObraSocialXPlanId", ObraSocialXPlanId).DbType = DbType.Int32
                    .AddWithValue("@CierreObraSocialTotalRecetas", CierreObraSocialTotalRecetas).DbType = DbType.Int32
                    .AddWithValue("@CierreObraSocialTotalFacturado", CierreObraSocialTotalFacturado).DbType = DbType.Double
                    .AddWithValue("@CierreObraSocialTotalACargo", CierreObraSocialTotalACargo).DbType = DbType.Double
                End With
                .Connection = cn
                .ExecuteNonQuery()
            End With
            Return True
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return False
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

    Public Shared Function obtenerCierresObrasSociales(ByVal CierreObraSocialId As Integer) As DataTable
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            cn = Conexion.Conectar
            Dim ds As New DataSet
            Dim da As New SqlClient.SqlDataAdapter("usp_cierreObraSocial_obtener", cn)
            With da.SelectCommand
                .CommandType = CommandType.StoredProcedure
                .Parameters.AddWithValue("@CierreObraSocialId", CierreObraSocialId).DbType = DbType.Int32
            End With
            da.Fill(ds, "CierresObrasSociales")

            Return ds.Tables("CierresObrasSociales")
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return Nothing
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

    Public Shared Function actualizarCierreObraSocial(ByVal CierreObraSocialId As Integer, ByVal CierreObraSocialMesId As Integer, ByVal CierreObraSocialAno As Integer, ByVal CierreObraSocialXPlanId As Integer, ByVal CierreObraSocialTotalRecetas As Integer, ByVal CierreObraSocialTotalFacturado As Double, ByVal CierreObraSocialTotalACargo As Double) As Boolean
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            cn = Conexion.Conectar
            Dim consulta As New SqlClient.SqlCommand
            With consulta
                .CommandText = "usp_cierreObraSocial"
                .CommandType = CommandType.StoredProcedure
                With .Parameters
                    .AddWithValue("@Accion", Constante.ACTUALIZAR).DbType = DbType.Int32
                    .AddWithValue("@CierreObraSocialId", CierreObraSocialId).DbType = DbType.Int32
                    .AddWithValue("@MesId", CierreObraSocialMesId).DbType = DbType.Int32
                    .AddWithValue("@CierreObraSocialAno", CierreObraSocialAno).DbType = DbType.Int32
                    .AddWithValue("@ObraSocialXPlanId", CierreObraSocialXPlanId).DbType = DbType.Int32
                    .AddWithValue("@CierreObraSocialTotalRecetas", CierreObraSocialTotalRecetas).DbType = DbType.Int32
                    .AddWithValue("@CierreObraSocialTotalFacturado", CierreObraSocialTotalFacturado).DbType = DbType.Double
                    .AddWithValue("@CierreObraSocialTotalACargo", CierreObraSocialTotalACargo).DbType = DbType.Double
                End With
                .Connection = cn
                .ExecuteNonQuery()
            End With
            Return True
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return False
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

    Public Shared Function eliminarCierreObraSocial(ByVal CierreObraSocialId As Integer) As Boolean
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            cn = Conexion.Conectar
            Dim consulta As New SqlClient.SqlCommand
            With consulta
                .CommandText = "usp_cierreObraSocial"
                .CommandType = CommandType.StoredProcedure
                With .Parameters
                    .AddWithValue("@Accion", Constante.ELIMINAR).DbType = DbType.Int32
                    .AddWithValue("@CierreObraSocialId", CierreObraSocialId).DbType = DbType.Int32
                    .AddWithValue("@CierreObraSocialMesId", Constante.MENOSUNO).DbType = DbType.Int32
                    .AddWithValue("@CierreObraSocialAno", Constante.MENOSUNO).DbType = DbType.Int32
                    .AddWithValue("@CierreObraSocialXPlanId", Constante.MENOSUNO).DbType = DbType.Int32
                    .AddWithValue("@CierreObraSocialTotalRecetas", Constante.MENOSUNO).DbType = DbType.Int32
                    .AddWithValue("@CierreObraSocialTotalFacturado", Constante.MENOSUNO).DbType = DbType.Double
                    .AddWithValue("@CierreObraSocialTotalACargo", Constante.MENOSUNO).DbType = DbType.Double
                End With
                .Connection = cn
                .ExecuteNonQuery()
            End With
            Return True
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return False
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

    Public Shared Function ObraSocialCerrada(ByVal CierreObraSocialMesId As Integer, ByVal CierreObraSocialAno As Integer, ByVal CierreObraSocialXPlanId As Integer) As Boolean
        Dim cn As SqlClient.SqlConnection = Nothing
        Dim cerrada As Boolean
        Dim consulta As String = "Select * From CierreObraSocial Where MesId = " & CierreObraSocialMesId.ToString & " And CierreObraSocialAno = " & CierreObraSocialAno.ToString & " And ObraSocialXPlanId = " & CierreObraSocialXPlanId.ToString
        Try
            cn = Conexion.Conectar
            Dim ds As New DataSet
            Dim da As New SqlClient.SqlDataAdapter(consulta, cn)
            da.Fill(ds, "CierresObrasSociales")

            If ds.Tables("CierresObrasSociales").Rows.Count = 0 Then
                cerrada = False
            Else
                cerrada = True
            End If

            Return cerrada
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return Nothing
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

    Public Shared Function obtenerTotalesOS() As Double()
        Dim cn As SqlClient.SqlConnection = Nothing
        Dim consulta As String = "Select SUM(CaratulaPresentacionRecetas) As 'Recetas', SUM(CaratulaPresentacionTotalFacturado) As 'Facturado', SUM(CaratulaPresentacionTotalCobrar) As 'ACargo' From ReporteCierre"
        Try
            cn = Conexion.Conectar
            Dim ds As New DataSet
            Dim da As New SqlClient.SqlDataAdapter(consulta, cn)
            da.Fill(ds, "TotalesOS")

            Dim totalRecetas As Integer = ds.Tables("TotalesOS").Rows(0)("Recetas")
            Dim totalFacturado As Double = ds.Tables("TotalesOS").Rows(0)("Facturado")
            Dim totalACargo As Double = ds.Tables("TotalesOS").Rows(0)("ACargo")

            Dim totales(2) As Double

            totales(0) = totalRecetas
            totales(1) = totalFacturado
            totales(2) = totalACargo

            Return totales
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return Nothing
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

    Public Shared Sub crearTablaReporteCierre(ByVal ObraSocialXPlanId As Integer, ByVal CierreObraSocialAno As Integer, ByVal MesId As Integer)
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            cn = Conexion.Conectar
            Dim consulta As New SqlClient.SqlCommand
            With consulta
                .CommandText = "usp_reporteCierreObraSocial"
                .CommandType = CommandType.StoredProcedure
                With .Parameters
                    .AddWithValue("@ObraSocialXPlanId", ObraSocialXPlanId).DbType = DbType.Int32
                    .AddWithValue("@Ano", CierreObraSocialAno).DbType = DbType.Int32
                    .AddWithValue("@Mes", MesId).DbType = DbType.Int32
                End With
                .Connection = cn
                .ExecuteNonQuery()
            End With
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Sub

    Public Shared Sub eliminarFilasTablaReporteCierre()
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            cn = Conexion.Conectar
            Dim consulta As New SqlClient.SqlCommand
            With consulta
                .CommandText = "Delete from ReporteCierre"
                .Connection = cn
                .ExecuteNonQuery()
            End With
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Sub

    Public Shared Function obtenerReporteCierre() As DataTable
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            cn = Conexion.Conectar
            Dim ds As New DataSet
            Dim da As New SqlClient.SqlDataAdapter("Select * From ReporteCierre", cn)
            da.Fill(ds, "ReporteCierre")

            Return ds.Tables("ReporteCierre")
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return Nothing
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

    Public Shared Function presentacionesCierreObraSociales(ByVal CierreObraSocialId As Integer) As DataTable
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            cn = Conexion.Conectar
            Dim ds As New DataSet
            Dim da As New SqlClient.SqlDataAdapter("usp_cierreObraSocial_presentaciones", cn)
            With da.SelectCommand
                .CommandType = CommandType.StoredProcedure
                .Parameters.AddWithValue("@CierreObraSocialId", CierreObraSocialId).DbType = DbType.Int32
            End With
            da.Fill(ds, "CierresObrasSociales")

            Return ds.Tables("CierresObrasSociales")
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return Nothing
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

    Public Shared Function obtenerXFiltroCierresObraSociales(ByVal CierreObraSocialAno As Integer, ByVal MesId As Integer, ByVal ObraSocialXPlanId As Integer, ByVal EstadoCierreObraSocialId As Integer) As DataTable
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            cn = Conexion.Conectar
            Dim ds As New DataSet
            Dim da As New SqlClient.SqlDataAdapter("usp_cierreObraSocial_obtenerXFiltro", cn)
            With da.SelectCommand
                .CommandType = CommandType.StoredProcedure
                .Parameters.AddWithValue("@CierreObraSocialAno", CierreObraSocialAno).DbType = DbType.Int32
                .Parameters.AddWithValue("@MesId", MesId).DbType = DbType.Int32
                .Parameters.AddWithValue("@ObraSocialXPlanId", ObraSocialXPlanId).DbType = DbType.Int32
                .Parameters.AddWithValue("@EstadoCierreObraSocialId", EstadoCierreObraSocialId).DbType = DbType.Int32
            End With
            da.Fill(ds, "CierresObrasSociales")

            Return ds.Tables("CierresObrasSociales")
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return Nothing
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

    Public Shared Function actualizarEstadoCierreObraSocial(ByVal CierreObraSocialId As Integer, ByVal EstadoCierreObraSocialId As Integer) As Boolean
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            cn = Conexion.Conectar
            Dim consulta As New SqlClient.SqlCommand
            With consulta
                .CommandText = "usp_actualizarEstadoCierreObraSocial"
                .CommandType = CommandType.StoredProcedure
                With .Parameters
                    .AddWithValue("@CierreObraSocialId", CierreObraSocialId).DbType = DbType.Int32
                    .AddWithValue("@EstadoCierreObraSocialId", EstadoCierreObraSocialId).DbType = DbType.Int32
                End With
                .Connection = cn
                .ExecuteNonQuery()
            End With
            Return True
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return False
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function


End Class
